import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @author osz-domosija
 */
public class TestRewards {

    public void start(final Connection connection) {
        try {           
            readCustomers(connection);

        } catch (final Exception e) {
            System.out.println("Error: " + e.getMessage());
            e.printStackTrace();
        }
    }

    private void readCustomers(final Connection connection) throws Exception {
        final PreparedStatement customerStatement = connection.prepareStatement("select customer_id from oc_customer where reward_state_id < 3");

        final ResultSet customerResultSet = customerStatement.executeQuery();
        while (customerResultSet.next()) {
            final float customerId = customerResultSet.getFloat("customer_id");

            final PreparedStatement crshStatement =
                    connection
                            .prepareStatement("select * from oc_customer_reward_state_history where customer_id = ? and state_id = 1 order by modify_date desc");
            crshStatement.setFloat(1, customerId);

            final ResultSet crshResultSet = crshStatement.executeQuery();
            Timestamp validFrom = null;
            if (crshResultSet.next()) {
                validFrom = crshResultSet.getTimestamp("modify_date");
            }

            if (validFrom != null) {
                final PreparedStatement crsh2Statement =
                        connection
                                .prepareStatement("select * from oc_customer_reward_state_history where customer_id = ? and state_id = 2 and modify_date > ? order by modify_date desc");
                crsh2Statement.setFloat(1, customerId);
                crsh2Statement.setTimestamp(2, validFrom);

                final ResultSet crsh2ResultSet = crsh2Statement.executeQuery();
                Timestamp validTo = null;
                if (crsh2ResultSet.next()) {
                    validTo = crsh2ResultSet.getTimestamp("modify_date");
                } else {
                    validTo = new Timestamp(new Date().getTime());
                }

                final BigDecimal total = getRewardsFromOrders(connection, customerId, 0, validFrom, validTo);

                final BigDecimal fromChildren = processChildren(connection, customerId, 1, validFrom, validTo);

                final int testValue = fromChildren.intValue() + total.intValue();
                final int fromProcessValue = sumRewards(connection, customerId).intValue();

                if (testValue != fromProcessValue) {
                    System.out.println(testValue + " != " + fromProcessValue + "\t\t\t" + "customerId: " + customerId);
                }
            }
        }
    }

    private BigDecimal
            processChildren(final Connection connection, final float customerId, final int level, final Timestamp from, final Timestamp to)
                    throws Exception {
        BigDecimal rewardSum = BigDecimal.ZERO;

        if (level < 4) {
            final List<Float> childList = getChildrenOf(connection, customerId);

            for (final Float childId : childList) {
                final BigDecimal total = getRewardsFromOrders(connection, childId, level, from, to);

                final BigDecimal rewardsFromChildren = processChildren(connection, childId, level + 1, from, to);
                rewardSum = rewardSum.add(new BigDecimal(rewardsFromChildren.intValue() + total.intValue()));
            }
        }

        return rewardSum;
    }

    private BigDecimal getRewardsFromOrders(final Connection connection, final Float customerId, final int level, final Timestamp from,
            final Timestamp to) throws Exception {
        BigDecimal total = BigDecimal.ZERO;

        final PreparedStatement orderStatement =
                connection.prepareStatement("select order_id from oc_order where customer_id = ? and order_status_id = ? and date_added between ? and ?");
        orderStatement.setFloat(1, customerId);
        orderStatement.setInt(2, RewardProcessor.PROCESSING_ORDER_STATUS_ID);
        orderStatement.setTimestamp(3, from);
        orderStatement.setTimestamp(4, to);

        final ResultSet orderResultSet = orderStatement.executeQuery();
        while (orderResultSet.next()) {
            final float orderId = orderResultSet.getFloat("order_id");
            total = total.add(getOrderTotal(connection, orderId, level));
        }

        return total;
    }

    private BigDecimal getOrderTotal(final Connection connection, final float orderId, final int level) throws Exception {
        // fizetett összeg
        final PreparedStatement orderStatement = connection.prepareStatement("select * from oc_order_total where order_id = ? and (code = 'sub_total' or code = 'reward')");
        orderStatement.setFloat(1, orderId);

        BigDecimal orderSubTotal = BigDecimal.ZERO;
        BigDecimal rewardTotal = BigDecimal.ZERO;
        final ResultSet orderResultSet = orderStatement.executeQuery();
        while (orderResultSet.next()) {
        	final String code = orderResultSet.getString(RewardProcessor.CODE);
        	if(code.equals(RewardProcessor.SUB_TOTAL)) {
        		orderSubTotal = orderResultSet.getBigDecimal(RewardProcessor.VALUE);	
        	} else if(code.equals(RewardProcessor.REWARD)) {
        		rewardTotal = orderResultSet.getBigDecimal(RewardProcessor.VALUE);
        	}
        }                

        if (orderSubTotal.compareTo(BigDecimal.ZERO) == 0) {
            throw new Exception("0 rendelesi osszeg. order_id: " + orderId);
        }
        
        BigDecimal payedProductTotal = orderSubTotal.add(rewardTotal);
        BigDecimal rewardsByOrder = BigDecimal.ZERO;
        
        if (level == 0) {
        	rewardsByOrder = new BigDecimal(payedProductTotal.multiply(RewardProcessor.SELF_PERCENTAGE).intValue());
        } else if (level == 1) {
        	rewardsByOrder = new BigDecimal(payedProductTotal.multiply(RewardProcessor.PARENT_PERCENTAGE).intValue());
        } else if (level == 2) {
        	rewardsByOrder = new BigDecimal(payedProductTotal.multiply(RewardProcessor.GRAND_PARENT_PERCENTAGE).intValue());
        } else if (level == 3) {
        	rewardsByOrder = new BigDecimal(payedProductTotal.multiply(RewardProcessor.GRAND_GRAND_PARENT_PERCENTAGE).intValue());
        }

        return rewardsByOrder;
    }

    private List<Float> getChildrenOf(final Connection connection, final Float customerId) throws SQLException {
        final List<Float> childList = new ArrayList<Float>();

        if (customerId != null && customerId != 0) {
            final PreparedStatement childStatement =
                    connection.prepareStatement("select customer_id, reward_state_id from oc_customer where parent_customer_id = ?");
            childStatement.setFloat(1, customerId);

            final ResultSet childResultSet = childStatement.executeQuery();
            while (childResultSet.next()) {
                childList.add(childResultSet.getFloat("customer_id"));
            }
        }

        return childList;
    }

    private BigDecimal sumRewards(final Connection connection, final float customerId) throws Exception {
        BigDecimal rewards = BigDecimal.ZERO;

        final PreparedStatement tmpRewardStatement =
                connection.prepareStatement("select SUM(points) as sum_points, process_date FROM " + RewardProcessor.TABLE_REWARDS
                        + " WHERE process_state = 0 AND customer_id = ?");
        tmpRewardStatement.setFloat(1, customerId);

        final ResultSet tmpResultSet = tmpRewardStatement.executeQuery();
        if (tmpResultSet.next()) {
            if (!tmpResultSet.wasNull()) {
                final BigDecimal _reward = tmpResultSet.getBigDecimal("sum_points");
                if (_reward != null) {
                    rewards = _reward;
                }
            }
        }

        return rewards;
    }

}
